﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
/// <summary>
/// Dataaccess class for airport, 
/// </summary>

namespace DBLayer
{
    public class AirportDB
    {
        //Variabler initialization
        private SqlUtility sqlUtility;
        private SqlConnection con;
        public AirportDB()
        {
            //constructor - creates a instans of sql connection 
            sqlUtility = new SqlUtility();
        }
        //Insert airport into database 
        public int InsertAirport(Airport _airport)
        {
            SqlConnection connect = sqlUtility.GetCon();
            const string query =
                "insert into airport (airportCode, airportName, country, city) Values (@airportCode, @airportName, @country, @city)";
           connect.Open();
           SqlCommand cmd = new SqlCommand(query, connect);

            cmd.Parameters.AddWithValue("@airportCode", _airport.AirportCode);
            cmd.Parameters.AddWithValue("@airportName", _airport.AirportName);
            cmd.Parameters.AddWithValue("@country", _airport.Country);
            cmd.Parameters.AddWithValue("@city", _airport.City);

            return sqlUtility.ExecuteData(cmd, connect);        
        }
        //find airport by string (airport code)
        public Airport FindAirportByCode(string _airportCode)
        {
            Airport retAirport = new Airport();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Airport WHERE airportCode = @airportCode";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@airportCode", _airportCode);
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        if (_reader.Read())
                        {
                            retAirport = BuildAirport(_reader);
                        }

                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
           
            return retAirport;
        }
        //find airport by string (airport name)
        public Airport FindAirportByName(string _airportName)
        {
            Airport retAirport = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Airport WHERE airportName = @airportName";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@airportName", _airportName);
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        
                        if (_reader.Read())
                        {
                            retAirport = BuildAirport(_reader);
                        }
                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retAirport;
        }
        //update airport by object of airport
        public int UpdateAirport(Airport _airport)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query =
                "UPDATE Airport Set airportCode = @airportCode, airportName = @airportName, country = @country, city = @city WHERE airportCode = @airportCode";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@airportName", _airport.AirportName);
            cmd.Parameters.AddWithValue("@country", _airport.Country);
            cmd.Parameters.AddWithValue("@city", _airport.City);
            cmd.Parameters.AddWithValue("@airportCode", _airport.AirportCode);
            return sqlUtility.ExecuteData(cmd,connect);
        }
        //build object airport from data from database
        private Airport BuildAirport(SqlDataReader _reader)
        {
            Airport temAirport = new Airport();

            temAirport.AirportCode = (string) _reader["airportCode"];
            temAirport.AirportName = (string) _reader["airportName"];
            temAirport.Country = (string) _reader["country"];
            temAirport.City = (string) _reader["city"];
            return temAirport;
        }
        //return a List<Airport> with all the airports in the database
        public List<Airport> SelectAll()
        {
            List<Airport> airports = new List<Airport>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Airport";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        while (_reader.Read())
                        {
                            Airport temAirport = BuildAirport(_reader);
                            airports.Add(temAirport);
                        }
                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }
            }
            con.Close();
            con.Dispose();
                
            return airports;
        }
        //remove Airport from database
        public int RemoveAirport(Airport _airport)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Airport WHERE airportCode = @airportCode";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@airportCode", _airport.AirportCode);
            return sqlUtility.ExecuteData(cmd,connect);
        }
        //Check if there is a connection between Airports
        public bool HasConnection(Airport _departure, Airport _arrival)
        {
            bool connected = false;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query =
                    "SELECT * from Flight WHERE startAirportCode = @startAirportCode AND endAirportCode = @endAirportCode";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@startAirportCode", _departure.AirportCode);
                    cmd.Parameters.AddWithValue("@endAirportCode", _arrival.AirportCode);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            connected = true;
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return connected;
        }
        //Returns a list of airports connected to the input
        public List<Airport> PossibleAirports(Airport _departure)
        {
            List<Airport> possibility = new List<Airport>();
            List<String> codes = new List<String>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query =
                    "SELECT endAirportCode from Airport a JOIN Flight f ON a.airportCode =  f.startAirportCode JOIN FlightInstance fi ON fi.routeId = f.routeId WHERE f.startAirportCode = @startAirportCode";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@startAirportCode", _departure.AirportCode);
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        while (_reader.Read())
                        {
                            String temp = (string)_reader["endAirportCode"];
                            codes.Add(temp);
                        }
                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }
                foreach (String str in codes)
                {
                    Airport result = FindAirportByCode(str);
                    possibility.Add(result);
                }


                con.Close();
                con.Dispose();
            }
            return possibility;
        }
    }
}
